package cn.kgc.layui.dao.impl;

import cn.kgc.layui.dao.UserDao;
import cn.kgc.layui.entity.User;
import cn.kgc.layui.utils.DruidUtil;
import com.mysql.jdbc.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.sound.sampled.Line;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: mosin
 * @version: v1.0  2022/9/15
 */
public class UserDaoImpl implements UserDao {
    @Override
    public List<User> selectPage(Integer start, Integer limit, User user) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select *  from layui  where 1=1 ";

        ArrayList<Object> params = new ArrayList<>();
        //  sql语句的动态拼接
        if(user!=null){
            if(!StringUtils.isEmptyOrWhitespaceOnly(user.getUsername())){
                sql+= "and username = ? ";
                params.add(user.getUsername());
            }

            if(!StringUtils.isEmptyOrWhitespaceOnly(user.getCity())){
                sql+= " and city = ? ";
                params.add(user.getCity());
            }
        }

        sql+=" limit ?,?";
        params.add(start);
        params.add(limit);

        System.out.println("sql = " + sql);
        System.out.println("params = " + params);

        try {
            List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class), params.toArray());
            return  users;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public Long selectTotal(User user) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = " select count(*) from layui where 1=1 ";

        ArrayList<Object> params = new ArrayList<>();
        //  sql语句的动态拼接
        if(user!=null){
            if(!StringUtils.isEmptyOrWhitespaceOnly(user.getUsername())){
                sql+= "and username = ? ";
                params.add(user.getUsername());
            }

            if(!StringUtils.isEmptyOrWhitespaceOnly(user.getCity())){
                sql+= " and city = ? ";
                params.add(user.getCity());
            }
        }
        System.out.println("sql = " + sql);
        System.out.println("params = " + params);
        try {
            Long count = queryRunner.query(sql, new ScalarHandler<Long>(),params.toArray());
            return  count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public Integer add(User usr) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "insert into layui(id,username,age,birth,city,sex)values(?,?,?,?,?,?)";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String bir = simpleDateFormat.format(usr.getBirth());
        try {
            int line = queryRunner.update(sql, usr.getId(), usr.getUsername(), usr.getAge(), bir, usr.getCity(), usr.getSex());
            return  line;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public Integer update(User usr) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update layui set username = ? ,age = ?,birth = ?,city = ?,sex = ? where id = ?";

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String bir = simpleDateFormat.format(usr.getBirth());
        try {
            int line = queryRunner.update(sql, usr.getUsername(), usr.getAge(), bir, usr.getCity(), usr.getSex(),usr.getId());
            return  line;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public Integer delete(String id) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "delete from layui where id = ?";
        try {
            int line = queryRunner.update(sql, id);
            return line;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw  new RuntimeException();
        }
    }
}
